CREATE procedure sp_asi_UpdateCompanyTitleUse
as
set nocount on
declare
@CompanyPurposes varchar(255),
@TitlePurposes varchar(255),
@PrefPurpose varchar(255),
@UseCompany bit,
@UseTitle bit,
@OrderNumber float,
@Id varchar(10)
select @CompanyPurposes =ShortValue from System_Params where ParameterName = 'Member_Control.PrintCompanyWithAddressString'
select @TitlePurposes =ShortValue from System_Params where ParameterName = 'Member_Control.PrintTitleWithAddressString'
select @UseCompany=0
select @UseTitle=0
declare Get_Orders cursor for
select ORDER_NUMBER,ST_ID
from Orders where STAGE not in ('CLOSED','CANCELED','COMPLETED')
open Get_Orders
fetch next from Get_Orders into @OrderNumber,@Id
WHILE @@FETCH_STATUS = 0
BEGIN
select @PrefPurpose=','+PURPOSE+',' from Name_Address
join Name on Name.ID=Name_Address.ID
where Name.MAIL_ADDRESS_NUM=Name_Address.ADDRESS_NUM and Name.ID=@Id
If charindex(@PrefPurpose,@CompanyPurposes)>0
begin
  select @UseCompany=1
end
If charindex(@PrefPurpose,@TitlePurposes)>0
begin
  select @UseTitle=1
end
update Orders set ST_PRINT_COMPANY=@UseCompany,ST_PRINT_TITLE=@UseTitle
where ORDER_NUMBER=@OrderNumber
update Order_Badge set ST_PRINT_COMPANY=@UseCompany,ST_PRINT_TITLE=@UseTitle
where ORDER_NUMBER=@OrderNumber and BADGE_TYPE='PRIMARY'
select @UseCompany=0
select @UseTitle=0
fetch next from Get_Orders into @OrderNumber,@Id
END
close Get_Orders
deallocate Get_Orders

GO
GRANT EXECUTE ON  [dbo].[sp_asi_UpdateCompanyTitleUse] TO [IMIS]
GO